Inventory Forecasting (Restocking and Cash Requirement) (Excel XLSX)

Updated August 17th, 2023 – The template now runs off monthly data structures instead of daily. This allows for easier scaling (adding 1,000s of SKUs) and better general usability. Additionally, an accounts payable balance will display based on configurable payment terms (up to three payment events for a given purchase) and visuals were upgraded.

The growth inputs are also much cleaner for the user to update / change as an annual growth rate is entered per year instead of per month and that applies to the average sales per month.

This template is designed to forecast the expected sales of inventory for up to a 72-month period. The model is driven off of average historical sales by month (accounts for seasonality) and the user can define % change from historical averages in order to drive the forecasted usage.

The final output is going to show the user how much inventory needs to be purchased per month and the cost of that.

Factors considered include:
•  Lead time
•  Starting inventory balance
•  Payment terms
•  Minimum inventory level (safety stock)
•  Months to reorder for

There are up to 19 SKUs this template will work for by default, but it is very easy to expand for as many as you need to account for. Other data summaries include the expected units reordered per month per SKU, running inventory balance per SKU by month and year, sales per month per SKU, revenue earned per SKU (per defined average selling price), and cost per month per SKU (per defined average cost per unit).

The model works off a expected units sold in order to get the most accurate forecast possible and can handle any input range for the various things that effect when inventory needs to be purchased, how slow or fast it will be used (per historical sales / seasonality logic).

The primary goal is to understand the expected cash requirement per month that is going to be needed to buy inventory with.